package com.xinyuan.utils;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.xinyuan.entity.*;
import com.xinyuan.service.AccessWork;
import com.xinyuan.service.WorkHourService;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;

import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Classname AmecoUtils
 * @Description TODO
 * @Date 2020/5/22 9:12
 * @Created by zfl
 */
public class AmecoUtils {
    /*
    *@Description 生成json
    *@param [message]
    *@return java.lang.String
    */
    @Autowired
    private WorkHourService workHourService1;
    public String getJson (Object obj) throws JsonProcessingException {
        //将message对象序列化为json
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(obj);
        return json;
    }
    /*
    *@Description 读取选定列的值
    *@param [file, i]
    *@return java.lang.String
    * 首检：CAMP_THRESHOL
    * 重检：CAMP_INTERV
    */
    public List readExcelByRow(String filePath,String str) throws IOException {
        Cell cell = null;
        int str_num = 0;
        File file = new File(filePath);
        if(file.length() == 0){
            System.out.println("上传文档为空，请重新上传！");
            ResultInfo info = new ResultInfo();
            info.setData(null);
            info.setErrorCode(1);
            info.setMsg("上传文件为空");
            return null;
        }else {
            List list = new ArrayList();
            Workbook workbook = null;
            FileInputStream stream = new FileInputStream(file);
            //处理不同版本的excel
            workbook = WorkbookFactory.create(stream);
            //第一个列表
            Sheet sheet = (Sheet) workbook.getSheetAt(0);
            int lastRowNum=sheet.getLastRowNum();
            //获取第三行
            Row row = sheet.getRow(2);
            //获取第一行的最后一个单元格的数值
            int lastCellNum=row.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                cell=row.getCell(i);
                //System.out.println(cell.getCellType());
                //cell.setCellType(CellType.STRING);
                if(cell.getStringCellValue().equalsIgnoreCase(str)) {
                    str_num = i;
                    break;
                }
            }
            System.out.println(str + ":" + str_num);
            try {
                for (int i = 3; i <= lastRowNum; i++) {
                    row = sheet.getRow(i);
                    if(row == null) {
                        list.add("0");
                        continue;
                    }
                    cell = row.getCell(str_num);
                    //System.out.println(cell.getCellType());
                    //cell.setCellType(cell.getCellType());
                    if(cell == null || cell.equals("") || cell.getCellType() == CellType.BLANK) {
                        list.add("0");
                    }else {
                        switch (cell.getCellType()) {
                            case FORMULA:list.add(cell.getCellFormula());
                                break;
                            case NUMERIC:list.add(cell.getNumericCellValue());
                                break;
                            case STRING:list.add(cell.getStringCellValue());
                                break;
                            default:
                                break;
                        }
                    }
                }
            } catch (Exception e) {
                System.out.println("单元格格式异常");
            }
            workbook.close();
            System.out.println("读取成功");
            return list;
        }
    }
    /*
    *@Description 写入excle
    *@param [list, str]
    *@return void
    */
    public void writeExcelByRow(List<String> list,String str,String filePath) throws IOException {
        Cell cell = null;
        int str_num = 0;
        File file = new File(filePath);
        Workbook workbook = null;
        FileInputStream in = new FileInputStream(file);
        FileOutputStream out = null;
        //处理不同版本的excel
        workbook = WorkbookFactory.create(in);
        //第一个列表
        Sheet sheet = (Sheet) workbook.getSheetAt(0);
        int lastRowNum=sheet.getLastRowNum();
        //获取第三行
        Row row = sheet.getRow(2);
        //获取第一行的最后一个单元格的数值
        int lastCellNum=row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell=row.getCell(i);
            //System.out.println(cell.getCellType());
            //cell.setCellType(CellType.STRING);
            if(cell.getStringCellValue().equalsIgnoreCase(str)) {
                str_num = i;
                break;
            }
        }
        System.out.println(str + ":" + str_num);
        try {
            for (int i = 3; i <= lastRowNum; i++) {
                row = sheet.getRow(i);
                cell = row.getCell(str_num);
                if(cell == null) {
                    cell = row.createCell(str_num);
                }
                cell.setCellValue(list.get(i -3));
            }
            out = new FileOutputStream(filePath);
            workbook.write(out);
        } catch (Exception e) {
            System.out.println("单元格格式异常");
        }
        workbook.close();
        System.out.println("写入成功");
    }
    /*
    *@Description servlet接收json数据,并将json转化为java对象
    *@param [request]
    *@return java.lang.String
    */
    public String acceptJSON(HttpServletRequest request) throws IOException {
        String acceptjson = "";
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader((ServletInputStream) request.getInputStream(), "utf-8"));
            StringBuffer sb = new StringBuffer("");
            String temp;
            while ((temp = br.readLine()) != null) {
                sb.append(temp);
            }
            acceptjson = sb.toString();
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        //json字符串
        String json = StringEscapeUtils.unescapeJavaScript(acceptjson);
        return json;
    }
    /*
    *@Description TODO
    *@param [json]
    *@return java.lang.String
    */
    public String ObjectToJson(String json) throws JsonProcessingException {
        ObjectMapper mapper = new ObjectMapper();
        ReqJson reqJson = null;
        ResultInfo info = new ResultInfo();
        String INFO;
        try {
            reqJson = mapper.readValue(json, ReqJson.class);
            System.out.println( "reqJson:" + reqJson);
            info.setMsg("录入正确!");
            info.setErrorCode(0);
            info.setData(reqJson);
        } catch (IOException e) {
            info.setMsg("录入错误！");
            info.setErrorCode(1);
            info.setData(null);
            //String json_info = StringEscapeUtils.unescapeJavaScript(getJson(info));
        }
        INFO = mapper.writeValueAsString(info);
        return INFO;
    }
    /*
    *@Description 转化
    *@param [list, method]
    *@return java.util.List<java.lang.Double>
    *method ：0 代表 FC
    *method : 1 代表 FH
    *method : 2 代表 MO
    */
    public List<Double> updateList(List<String> list,int method ) {
        List<Double> newList = new ArrayList<Double>();
        for (String item : list) {
            switch (method) {
                case 0:
                    Pattern r0 = Pattern.compile("([0-9]+)[^0-9]?FC");
                    Matcher m0 = r0.matcher(item);
                    if (m0.find()) {
                        if (m0.groupCount() >= 1) {
                            newList.add(Double.parseDouble(m0.group(1)));
                        }
                    }
                    break;
                case 1:
                    Pattern r1 = Pattern.compile("([0-9]+)[^0-9]?FH");
                    Matcher m1 = r1.matcher(item);
                    if (m1.find()) {
                        if (m1.groupCount() >= 1) {
                            newList.add(Double.parseDouble(m1.group(1)));
                        }
                    }
                    break;
                case 2:
                    Double result = 0.0;
                    Pattern r2_d = Pattern.compile("([0-9]+)[^0-9]?DY");
                    Pattern r2_m = Pattern.compile("([0-9]+)[^0-9]?MO");
                    Pattern r2_y = Pattern.compile("([0-9]+)[^0-9]?YR");
                    Matcher m2_d = r2_d.matcher(item);
                    Matcher m2_m = r2_m.matcher(item);
                    Matcher m2_y = r2_y.matcher(item);
                    if (m2_d.find() || m2_m.find() || m2_y.find()) {
                        m2_d.reset();
                        m2_m.reset();
                        m2_y.reset();
                        if (m2_d.groupCount() >= 1 && m2_d.find()) {
                            result += Integer.parseInt(m2_d.group(1)) / 30.0;
                        }
                        if (m2_m.groupCount() >= 1 && m2_m.find()) {
                            result += Integer.parseInt(m2_m.group(1));
                        }
                        if (m2_y.groupCount() >= 1 && m2_y.find()) {
                            result += Integer.parseInt(m2_y.group(1)) * 12;
                        }
                        newList.add(result);
                    }
                    break;
                default:
                    //todo 没有对应的method，可以抛出异常
                    System.out.println("method 不匹配");
            }
        }
        System.out.println("转化成功");
        return newList;
    }

    public List<Double> conversionList(List<String> list,int method ) {
        List<Double> newList = new ArrayList<Double>();
        for (String item : list) {
            switch (method) {
                case 0:
                    Pattern r0 = Pattern.compile("([0-9]+)[^0-9]?FC");
                    Matcher m0 = r0.matcher(item);
                    if (m0.find()) {
                        if (m0.groupCount() >= 1) {
                            newList.add(Double.parseDouble(m0.group(1)));
                        }
                    }else {
                        newList.add(0.0);
                    }
                    break;
                case 1:
                    Pattern r1 = Pattern.compile("([0-9]+)[^0-9]?FH");
                    Matcher m1 = r1.matcher(item);
                    if (m1.find()) {
                        if (m1.groupCount() >= 1) {
                            newList.add(Double.parseDouble(m1.group(1)));
                        }
                    }else {
                        newList.add(0.0);
                    }
                    break;
                case 2:
                    Double result = 0.0;
                    Pattern r2_d = Pattern.compile("([0-9]+)[^0-9]?DY");
                    Pattern r2_m = Pattern.compile("([0-9]+)[^0-9]?MO");
                    Pattern r2_y = Pattern.compile("([0-9]+)[^0-9]?YR");
                    Matcher m2_d = r2_d.matcher(item);
                    Matcher m2_m = r2_m.matcher(item);
                    Matcher m2_y = r2_y.matcher(item);
                    if (m2_d.find() || m2_m.find() || m2_y.find()) {
                        m2_d.reset();
                        m2_m.reset();
                        m2_y.reset();
                        if (m2_d.groupCount() >= 1 && m2_d.find()) {
                            result += Integer.parseInt(m2_d.group(1)) / 30.0;
                        }
                        if (m2_m.groupCount() >= 1 && m2_m.find()) {
                            result += Integer.parseInt(m2_m.group(1));
                        }
                        if (m2_y.groupCount() >= 1 && m2_y.find()) {
                            result += Integer.parseInt(m2_y.group(1)) * 12;
                        }
                        newList.add(result);
                    }else {
                        newList.add(0.0);
                    }
                    break;
                default:
                    //todo 没有对应的method，可以抛出异常
                    System.out.println("method 不匹配");
            }
        }
        System.out.println("转化成功");
        return newList;
    }
    public Message getCount(List<Double> list, int interval, double blur) {
        Message message = new Message();
        List<User> list1 = new ArrayList<>();
        double minInterv =  Collections.min(list);//获取最小值
        double maxInterv =  Collections.max(list);//获取最大值
        int numInterv = (int)(maxInterv - minInterv) / interval;
        //判断最小值和interval的大小关系
        int num = (int)minInterv / interval;
        User[] users = new User[numInterv + 1];
        int[] count = new int[numInterv + 1];
        for (int i = 0; i <= numInterv; i++) {
            users[i] = new User();//对象数组只有实例化才能付初值
            users[i].setInterv((num + i) * interval);
            users[i].setCount(0);
            count[i] = 0;

        }
        //遍历list
        Iterator<Double> it = list.iterator();
        while(it.hasNext()){
            double temp = it.next();
            for (int i = 0; i <= numInterv; i++) {
                if(temp >= (num + i) * interval * (1 - blur) && temp <= (num + i) * interval * (1 + blur)) {
                    count[i] ++;
                    break;
                }
            }
        }
        for (int i = 0; i <= numInterv ; i++) {
            users[i].setCount(count[i]);
            list1.add(users[i]);
        }
        message.setMaxInterv(maxInterv);
        message.setMinInterv(minInterv);
        message.setData(list1);
        message.setErrCode(1);
        message.setMsg("输出成功");
        return message;
    }
    /*
    *@Description 获取指定列
    *@param [filePath, str]
    *@return int
    */
    public int getColumn(String filePath,String str) throws IOException {
        Cell cell = null;
        int str_num = 0;
        File file = new File(filePath);
        List<String> list = new ArrayList<String>();
        Workbook workbook = null;
        FileInputStream stream = new FileInputStream(file);
        //处理不同版本的excel
        workbook = WorkbookFactory.create(stream);
        /*if(filePath.toLowerCase().endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
        }else if(filePath.toLowerCase().endsWith(".xls")) {
            workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
        }*/
        //第一个列表
        Sheet sheet = (Sheet) workbook.getSheetAt(0);
        int lastRowNum=sheet.getLastRowNum();
        //获取第三行
        Row row = sheet.getRow(2);
        //获取第一行的最后一个单元格的数值
        int lastCellNum=row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell=row.getCell(i);
            //cell.setCellType(CellType.STRING);
            if(cell.getStringCellValue().equalsIgnoreCase(str)) {
                str_num = i;
                break;
            }
        }
        return str_num;
    }
    /*
    *@Description 计算工作工时
    *@param [list_1, list_2]
    *@return java.util.List
    */
    public List countWorkHour(List<Double> list_1,List<Double> list_2){
        List list = new ArrayList();
        double temp;
        for (int i = 0; i < list_1.size(); i++) {
            temp = list_1.get(i) + list_2.get(i);

            list.add(temp);
        }
        return list;
    }
    /*
    *@Description 上传文档
    *@param [req]
    *@return java.lang.String
    */
    public String uploadFile(HttpServletRequest req, HttpServletResponse resp, String type, String id) throws IOException {
        String filePath = "";
        try {
            String realPath = "C:\\upload";
            System.out.println("文件存放位置:" + realPath);
            //判断存放上传文件的目录是否存在（不存在则创建）
            File f = new File(realPath);
            if (!f.exists() && !f.isDirectory()) {
                System.out.println("目录或文件不存在! 创建目标目录。");
                f.mkdir();
            }
            realPath = "C:\\upload\\" + id;
            File file = new File(realPath);
            if (!file.exists() && !file.isDirectory()) {
                System.out.println("目录或文件不存在! 创建目标目录。");
                file.mkdir();
            }
            /**
             * 使用Apache文件上传组件处理文件上传步骤：
             *
             * */
            //1、设置环境:创建一个DiskFileItemFactory工厂
            DiskFileItemFactory factory = new DiskFileItemFactory();
            //2、核心操作类:创建一个文件上传解析器。
            ServletFileUpload upload = new ServletFileUpload(factory);
            //解决上传"文件名"的中文乱码
            upload.setHeaderEncoding("UTF-8");
            //4、使用ServletFileUpload解析器解析上传数据，解析结果返回的是一个List<FileItem>集合，每一个FileItem对应一个Form表单的输入项
            List<FileItem> items = upload.parseRequest(req);
            for (FileItem item : items) {
                //如果fileItem中封装的是普通输入项的数据（输出名、值）
                if (item.isFormField()) {
                    String filedName = item.getFieldName();//普通输入项数据的名
                    //解决普通输入项的数据的中文乱码问题
                    String filedValue = item.getString("UTF-8");//普通输入项的值
                    System.out.println("普通字段:" + filedName + "==" + filedValue);
                } else {
                    //如果fileItem中封装的是上传文件，得到上传的文件名称，
                    String fileName = item.getName();//上传文件的名
                    //多个文件上传输入框有空 的 异常处理
                    if (fileName == null || "".equals(fileName.trim())) {  //去空格是否为空
                        continue;// 为空，跳过当次循环，  第一个没输入则跳过可以继续输入第二个
                    }
                    //注意：不同的浏览器提交的文件名是不一样的，有些浏览器提交上来的文件名是带有路径的，如：  c:\a\b\1.txt，而有些只是单纯的文件名，如：1.txt
                    //处理上传文件的文件名的路径，截取字符串只保留文件名部分。//截取留最后一个"\"之后，+1截取向右移一位（"\a.txt"-->"a.txt"）
                    fileName = fileName.substring(fileName.lastIndexOf("\\") + 1);
                    if(fileName.endsWith(".xlsx")){
                        fileName = type +".xlsx";
                    }else if(fileName.endsWith(".xls")){
                        fileName = type + ".xls";
                    }
                    //拼接上传路径。存放路径+上传的文件名
                    filePath = realPath + "\\" + fileName;
                    //构建输入输出流
                    InputStream in = item.getInputStream(); //获取item中的上传文件的输入流
                    OutputStream out = new FileOutputStream(filePath); //创建一个文件输出流
                    //创建一个缓冲区
                    byte b[] = new byte[1024];
                    //判断输入流中的数据是否已经读完的标识
                    int len = -1;
                    //循环将输入流读入到缓冲区当中，(len=in.read(buffer)) !=-1就表示in里面还有数据
                    while ((len = in.read(b)) != -1) {  //没数据了返回-1
                        //使用FileOutputStream输出流将缓冲区的数据写入到指定的目录(savePath+"\\"+filename)当中
                        out.write(b, 0, len);
                    }
                    //关闭流
                    out.close();
                    in.close();
                    //删除临时文件
                    try {
                        Thread.sleep(3000);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                    item.delete();//删除处理文件上传时生成的临时文件
                }
            }
        } catch (FileUploadException e) {
            //e.printStackTrace();
            throw new RuntimeException("服务器繁忙，文件上传失败");
        }
        try {
            File file = new File(filePath);
            if(file.exists()) {
                if(file.length() != 0) {
                    ResultInfo info = new ResultInfo();
                    info.setData(null);
                    info.setErrorCode(0);
                    info.setMsg("上传文件成功");
                    resp.setContentType("application/json;charset=utf-8");
                    resp.getWriter().write(getJson(info));
                    return filePath;
                }else {
                    ResultInfo info = new ResultInfo();
                    info.setData(null);
                    info.setErrorCode(1);
                    info.setMsg("上传文件为空");
                    resp.setContentType("application/json;charset=utf-8");
                    resp.getWriter().write(getJson(info));
                    return null;
                }
            }else {
                ResultInfo info = new ResultInfo();
                info.setData(null);
                info.setErrorCode(1);
                info.setMsg("上传文件失败");
                resp.setContentType("application/json;charset=utf-8");
                resp.getWriter().write(getJson(info));
                return null;
            }
        } catch (Exception e) {
            return null;
        }
    }
    /*
     * method ：0 代表 FC
     * method : 1 代表 FH
     * method : 2 代表 MO
     * */
    public void saveData(String fileID, WorkHourService workHourService, Check check) throws IOException {

        String filePath = "C:\\upload\\" + fileID + "\\MTOP.xlsx";
        double min;
        int floor;
        double MTOP_ITEM_MH,MTOP_OPERATION_MH,THRESHOL_FH,THRESHOL_FC,THRESHOL_MO,INTERV_FH,INTERV_FC,INTERV_MO,C_FH,C_FC,C_MO,A_FH,A_FC,A_MO;
        String A_THRESHOL,C_THRESHOL,A_INTERV,C_INTERV,MTOP_THRESHOL,MTOP_INTERV,str;
        //camp首检
        List<String > LIST_CAMP_THRESHOL = new ArrayList();

        LIST_CAMP_THRESHOL = readExcelByRow(filePath, "CAMP_THRESHOL");
        List<Double> LIST_THRESHOL_FC = conversionList(LIST_CAMP_THRESHOL,0);

        List<Double> LIST_THRESHOL_FH = conversionList(LIST_CAMP_THRESHOL,1);
        List<Double> LIST_THRESHOL_MO = conversionList(LIST_CAMP_THRESHOL,2);
        //camp重检
        List<String> LIST_CAMP_INTERV = readExcelByRow(filePath,"CAMP_INTERV");

        List<Double> LIST_INTERV_FC = conversionList(LIST_CAMP_INTERV,0);

        List<Double> LIST_INTERV_FH = conversionList(LIST_CAMP_INTERV,1);
        List<Double> LIST_INTERV_MO = conversionList(LIST_CAMP_INTERV,2);
        //接近工时access字段
        //List<String> LIST_MTOP_ACCESS = readExcelByRow(filePath,"MTOP_ACCESS");

        //mtop首检和重检
        List<String> LIST_MTOP_THRESHOL = new ArrayList<String>();
        List<String> LIST_MTOP_INTERV = new ArrayList<String>();
        //acmp首检A检间隔
        List<String> LIST_THRESHOL_A = new ArrayList<String>();
        List<String> LIST_THRESHOL_C = new ArrayList<String>();
        List<String> LIST_INTERV_A = new ArrayList<String>();
        List<String> LIST_INTERV_C = new ArrayList<String>();
        //操作工时
        //List<String> LIST_MTOP_OPERATION_MH = readExcelByRow(filePath,"MTOP_OPERATION_MH");

        //条目工时
        //List<String> LIST_MTOP_ITEM_MH = readExcelByRow(filePath,"MTOP_ITEM_MH");

        for (int i = 0; i < LIST_THRESHOL_FH.size(); i++) {
            THRESHOL_FH = (double)(Math.round(LIST_THRESHOL_FH.get(i)*1000)/1000.00);
            THRESHOL_FC = (double)(Math.round(LIST_THRESHOL_FC.get(i)*1000)/1000.00);
            THRESHOL_MO = (double)(Math.round(LIST_THRESHOL_MO.get(i)*1000)/1000.00);
            INTERV_FH = (double)(Math.round(LIST_INTERV_FH.get(i)*1000)/1000.00);
            INTERV_FC = (double)(Math.round(LIST_INTERV_FC.get(i)*1000)/1000.00);
            INTERV_MO = (double)(Math.round(LIST_INTERV_MO.get(i)*1000)/1000.00);
            //MTOP_OPERATION_MH = Double.parseDouble(LIST_MTOP_OPERATION_MH.get(i));
            //MTOP_OPERATION_MH = (double)(Math.round(MTOP_OPERATION_MH*100)/100.0);
            //MTOP_ITEM_MH = Double.parseDouble(LIST_MTOP_ITEM_MH.get(i));
            //MTOP_ITEM_MH = (double)(Math.round(MTOP_ITEM_MH*100)/100.0);
            //首检
            C_FH = THRESHOL_FH / check.getFh_c();
            C_FC = THRESHOL_FC / check.getFc_c();
            C_MO = THRESHOL_MO / check.getMo_c();
            min = getMin(C_FH, C_FC, C_MO);
            if(min >= 1.0) {
                floor = (int) Math.floor(min);
                str = String.valueOf(floor) + " C";
                LIST_MTOP_THRESHOL.add(str);
                LIST_THRESHOL_C.add(str);
                LIST_THRESHOL_A.add("0 A");
                A_THRESHOL = "0 A";
                C_THRESHOL = str;
                MTOP_THRESHOL = C_THRESHOL;
            }else {
                A_FH = THRESHOL_FH / check.getFh_a();
                A_FC = THRESHOL_FC / check.getFc_a();
                A_MO = THRESHOL_MO / check.getMo_a();
                min = getMin(A_FH,A_FC,A_MO);
                floor = (int) Math.floor(min);
                str = String.valueOf(floor) + " A";
                LIST_MTOP_THRESHOL.add(str);
                LIST_THRESHOL_A.add(str);
                LIST_THRESHOL_C.add("0 C");
                A_THRESHOL = str;
                C_THRESHOL = "0 C";
                MTOP_THRESHOL = A_THRESHOL;
            }
            //重检
            C_FH = INTERV_FH / check.getFh_c();
            C_FC = INTERV_FC / check.getFc_c();
            C_MO = INTERV_MO / check.getMo_c();

            min = getMin(C_FH, C_FC, C_MO);
            if(min >= 1.0) {
                floor = (int) Math.floor(min);
                str = String.valueOf(floor) + " C";
                LIST_MTOP_INTERV.add(str);
                LIST_INTERV_A.add("0 A");
                LIST_INTERV_C.add(str);
                A_INTERV = "0 A";
                C_INTERV = str;
                MTOP_INTERV = C_INTERV;
            }else {
                A_FH = INTERV_FH / check.getFh_a();
                A_FC = INTERV_FC / check.getFc_a();
                A_MO = INTERV_MO / check.getMo_a();
                min = getMin(A_FH,A_FC,A_MO);
                floor = (int) Math.floor(min);
                str = String.valueOf(floor) + " A";
                LIST_MTOP_INTERV.add(str);
                LIST_INTERV_A.add(str);
                LIST_INTERV_C.add("0 C");
                A_INTERV = str;
                C_INTERV = "0 C";
                MTOP_INTERV = A_INTERV;
            }
            //注意注入问题workHourService注入失败会报空指针异常 null
            workHourService.insertWorkHour(THRESHOL_FH,THRESHOL_FC,THRESHOL_MO,INTERV_FH,INTERV_FC,INTERV_MO,A_THRESHOL,C_THRESHOL,A_INTERV,C_INTERV,MTOP_THRESHOL,MTOP_INTERV);
        }
        //写入首检
        writeExcelByRow(LIST_MTOP_THRESHOL,"MTOP_THRESHOL",filePath);
        //写入重检
        writeExcelByRow(LIST_MTOP_INTERV,"MTOP_INTERV",filePath);

    }
    /*
    *@Description 获取最小值
    *@param [d1, d2, d3]
    *@return double
    */
    public double getMin(double d1,double d2,double d3) {
        double[] dou = {d1, d2, d3};
        Arrays.sort(dou);
        if(dou[0] > 0) {
            return dou[0];
        }else if(dou[1] > 0) {
            return dou[1];
        }else {
            return dou[2];
        }
    }

    public List<CheckMessage> getWorkHour(String fileID) throws IOException {
        String filePath = "C:\\upload\\" + fileID + "\\MTOP.xlsx";
        double MTOP_OPERATION_MH,MTOP_ITEM_MH;
        //操作工时
        List<String> LIST_MTOP_OPERATION_MH = readExcelByRow(filePath,"MTOP_OPERATION_MH");

        //条目工时
        List<String> LIST_MTOP_ITEM_MH = readExcelByRow(filePath,"MTOP_ITEM_MH");

        List<String> LIST_MTOP_THRESHOL = readExcelByRow(filePath,"MTOP_THRESHOL");
        List<String> LIST_MTOP_INTERV = readExcelByRow(filePath,"MTOP_INTERV");
        //工作工时
        List<CheckMessage> listWORKHOUR = new ArrayList<CheckMessage>();
        for (int i = 0; i < LIST_MTOP_OPERATION_MH.size(); i++) {
            CheckMessage checkMessage = new CheckMessage();
            MTOP_OPERATION_MH = Double.parseDouble(LIST_MTOP_OPERATION_MH.get(i));
            MTOP_OPERATION_MH = (double)(Math.round(MTOP_OPERATION_MH*1000)/1000.00);
            MTOP_ITEM_MH = Double.parseDouble(LIST_MTOP_ITEM_MH.get(i));
            MTOP_ITEM_MH = (double)(Math.round(MTOP_ITEM_MH*1000)/1000.00);
            checkMessage.setWorkHour(MTOP_OPERATION_MH + MTOP_ITEM_MH);
            checkMessage.setMtop_THRESHOL(LIST_MTOP_THRESHOL.get(i));
            checkMessage.setMtop_INTERV(LIST_MTOP_INTERV.get(i));
            listWORKHOUR.add(checkMessage);
        }
        return listWORKHOUR;
    }

    public Total countWorkHour(List<CheckMessage> list,TimeType timeType) {
        int count_A,count_C,num_a;
        String strThreshol,strInterv;
        Total total = new Total();
        total.setATotalHour(0.0);
        total.setCTotalHour(0.0);
        double value;
        //计算A检
        count_A = timeType.getATime().getMaxTime() * 12 / timeType.getATime().getMinTime();
        //计算C检
        count_C = timeType.getCTime().getMaxTime() * 12 / timeType.getCTime().getMinTime();
        Map<Integer, Double> amap = new HashMap();
        Map<Integer,Double> cmap = new HashMap();
        for (int i = 1; i <= count_A; i++) {
            amap.put(i,0.0);
        }
        for (int i = 1; i <= count_C; i++) {
            cmap.put(i,0.0);
        }
        for (int i = 0; i < list.size(); i++) {
            //首检
            strThreshol = list.get(i).getMtop_THRESHOL();
            //重检
            strInterv = list.get(i).getMtop_INTERV();
            //计算A检
            if(strThreshol.endsWith("A")) {
                //比较两个是否相等
                amap = inputMap(list, count_A, strThreshol, strInterv, amap, i);
                //记录总工时
                num_a = Integer.parseInt(strThreshol.substring(0, strThreshol.indexOf(" ")));
                if(num_a <= count_A) {
                    value = total.getATotalHour() + list.get(i).getWorkHour();
                    total.setATotalHour(value);
                }

            }else {     //计算C检
                cmap = inputMap(list, count_C, strThreshol, strInterv, cmap, i);
                //记录总工时
                num_a = Integer.parseInt(strInterv.substring(0,strInterv.indexOf(" ")));
                if(num_a <= count_C){
                    value = total.getCTotalHour() + list.get(i).getWorkHour();
                    total.setCTotalHour(value);
                }
            }
        }
        total.setAmap(amap);
        total.setCmap(cmap);
        return total;
    }

    public Map inputMap(List<CheckMessage> list, int count, String strThreshol, String strInterv, Map<Integer, Double> map, int i) {
        int temp,num_a,multi,num_b;
        double value;
        temp = strThreshol.compareTo(strInterv);
        if(temp == 0){
            //A检的整数值
            num_a = Integer.parseInt(strThreshol.substring(0,strThreshol.indexOf(" ")));
            if(num_a <= 0){
                return map;
            }else {
                //计算倍数
                multi = count / num_a;
                for (int j = 1; j <= multi; j++) {
                    value = map.get(num_a * j);
                    value = value + list.get(i).getWorkHour();
                    map.put(num_a * j , value);
                }
            }
        }else if(temp > 0) {
            //前值较大时
            //先计算前值再计算后值
            //A检的整数值
            num_a = Integer.parseInt(strThreshol.substring(0,strThreshol.indexOf(" ")));
            if(num_a > 0) {
                //计算大的
                multi = count / num_a;
                for (int j = 1; j <= multi; j++) {
                    value = map.get(num_a * j);
                    value = value + list.get(i).getWorkHour();
                    map.put(num_a * j , value);
                }
            }
            //计算小的
            num_b = Integer.parseInt(strInterv.substring(0,strInterv.indexOf(" ")));
            //System.out.println(num_a);
            if(num_b > 0) {
                multi = count / num_b;
                for (int j = 2; j <= multi; j++) {
                    if((j % num_a) != 0 && (j / num_a) >= 1) {
                        value = map.get(num_b * j);
                        value = value + list.get(i).getWorkHour();
                        map.put(num_b * j , value);
                    }

                }
            }
        }else {
            //计算前值较小时
            //先计算前值再计算后值
            num_a = Integer.parseInt(strThreshol.substring(0,strThreshol.indexOf(" ")));
            if(num_a > 0) {
                multi = count / num_a;
                for (int j = 1; j <= multi; j++) {
                    value = map.get(num_a * j);
                    value = value + list.get(i).getWorkHour();
                    map.put(num_a * j , value);
                }
            }
            num_b = Integer.parseInt(strInterv.substring(0,strInterv.indexOf(" ")));
            if(num_b > 0){
                multi = count / num_b;
                for (int j = 1; j <= multi; j++) {
                    if(num_a > 0) {
                        if((j % num_a) != 0) {
                            value = map.get(num_b * j);
                            value = value + list.get(i).getWorkHour();
                            map.put(num_b * j , value);
                        }
                    }else {
                        value = map.get(num_b * j);
                        value = value + list.get(i).getWorkHour();
                        map.put(num_b * j , value);
                    }

                }
            }
        }
        return map;
    }

    public AccessWork designExcel(String fileID,Total total,TimeType timeType,String fileName) throws IOException {
        String filePath = "C:\\upload\\" + fileID;
        String filePathMtop = "C:\\upload\\" + fileID + "\\MTOP.xlsx";
        String filePathAccess = "C:\\upload\\" + fileID + "\\TIME.xls";
        double totalHour = 0.0;
        //创建一个workbook,对应一个excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //在workbook中添加一个sheet,对应excel中的sheet
        Sheet sheet = wb.createSheet("工时");
        HSSFCellStyle contentStyle = createContentCellStyle(wb);
        AccessWork accessWork = new AccessWork();
        accessWork.calculateAll(filePathMtop,filePathAccess,timeType.getATime().getMaxTime(),timeType.getATime().getMinTime(),
                timeType.getCTime().getMaxTime(),timeType.getCTime().getMinTime());
        /*for (int i = 1; i <= accessWork.accessSetMapA.size(); i++) {
            System.out.println(i + " A");
            System.out.println(accessWork.accessSetMapA.get(i));
        }

        for (int i = 1; i <= accessWork.accessSetMapC.size(); i++) {
            System.out.println(i + " C");
            System.out.println(accessWork.accessSetMapC.get(i));
        }*/
        /*System.out.println(12 + " A");
        System.out.println(accessWork.accessSetMapA.get(12));
        System.out.println(accessWork.accessSetMapA.get(12).size());
        System.out.println(18 + " A");
        System.out.println(accessWork.accessSetMapA.get(18));
        System.out.println(accessWork.accessSetMapA.get(18).size());*/
        //行号
        int rowNum = 0;
        //创建第一行，索引从0开始
        Row row0 = sheet.createRow(rowNum++);
        String str = "工时";
        Cell c00 = row0.createCell(1);
        c00.setCellValue(str);
        c00.setCellStyle(contentStyle);
        //c00.setCellStyle();
        //合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));
        Cell c01 = row0.createCell(0);
        c01.setCellValue("工作包");
        c01.setCellStyle(contentStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//工作包
        //第二行
        Row row1 = sheet.createRow(rowNum++);
        String[] row_first = {"工作包","接近工时","工作工时","总工时"};
        for (int i = 1; i < row_first.length; i++) {
            Cell tempCell = row1.createCell(i);
            tempCell.setCellValue(row_first[i]);
            System.out.println(row_first[i]);
            tempCell.setCellStyle(contentStyle);
        }
        String strA;
        //A包
        for (int i = 1; i <= timeType.getATime().getMaxTime() * 12 / timeType.getATime().getMinTime(); i++) {
            Row aRow = sheet.createRow(rowNum++);
            for (int j = 0; j < 4; j++) {
                if(j == 0) {
                    Cell aCell = aRow.createCell(j);
                    strA = String.valueOf(i);
                    aCell.setCellValue(strA + " A");
                    aCell.setCellStyle(contentStyle);
                }else if(j == 1){
                    Cell aCell = aRow.createCell(j);
                    totalHour = accessWork.accessTimeMapA.get(i);
                    totalHour = Math.round(totalHour * 1000) / 1000.00;
                    aCell.setCellValue(totalHour);
                    aCell.setCellStyle(contentStyle);
                }else if(j == 2) {
                    Cell aCell = aRow.createCell(j);
                    totalHour = total.getAmap().get(i);
                    totalHour = Math.round(totalHour * 1000) / 1000.00;
                    strA = String.valueOf(totalHour);
                    aCell.setCellValue(strA);
                    aCell.setCellStyle(contentStyle);
                }else {
                    Cell aCell = aRow.createCell(j);
                    //MTOP_ITEM_MH = (double)(Math.round(MTOP_ITEM_MH*100)/100.0)
                    totalHour = total.getAmap().get(i) + accessWork.accessTimeMapA.get(i);
                    totalHour = Math.round(totalHour * 1000) / 1000.00;
                    strA = String.valueOf(totalHour);
                    aCell.setCellValue(strA);
                    aCell.setCellStyle(contentStyle);
                }
            }
        }

        //C包
        for (int i = 1; i <= timeType.getCTime().getMaxTime() * 12 / timeType.getCTime().getMinTime(); i++) {
            Row aRow = sheet.createRow(rowNum++);
            for (int j = 0; j < 4; j++) {
                if(j == 0) {
                    Cell aCell = aRow.createCell(j);
                    strA = String.valueOf(i);
                    aCell.setCellValue(strA + " C");
                    aCell.setCellStyle(contentStyle);
                }else if(j == 1){
                    Cell aCell = aRow.createCell(j);
                    totalHour = accessWork.accessTimeMapC.get(i);
                    totalHour = Math.round(totalHour * 1000) / 1000.00;
                    aCell.setCellValue(totalHour);
                    aCell.setCellStyle(contentStyle);
                }else if(j == 2) {
                    Cell aCell = aRow.createCell(j);
                    totalHour = total.getCmap().get(i);
                    totalHour = Math.round(totalHour * 1000) / 1000.00;
                    strA = String.valueOf(totalHour);
                    aCell.setCellValue(strA);
                    aCell.setCellStyle(contentStyle);
                }else {
                    Cell aCell = aRow.createCell(j);
                    totalHour = total.getCmap().get(i) + accessWork.accessTimeMapC.get(i);
                    totalHour = Math.round(totalHour * 1000) / 1000.00;
                    strA = String.valueOf(totalHour);
                    aCell.setCellValue(strA);
                    aCell.setCellStyle(contentStyle);
                }
            }
        }

        //将文档保存在本地
        File file = new File(filePath);
        if (!file.exists() && !file.isDirectory()) {
            System.out.println("目录或文件不存在! 创建目标目录。");
            file.mkdir();
        }
        FileOutputStream output = new FileOutputStream(filePath + "\\" + fileName);
        wb.write(output);
        output.close();
        return accessWork;
    }

    /**
     *  设置表格内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

        //设置字体
        HSSFFont font = (HSSFFont)wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short)12);

        return cellStyle;
    }

}

